//
//  DataBaseHandler.m
//  DolphinMusic
//
//  Created by dlios on 15-3-27.
//  Copyright (c) 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseHandler.h"
#import "Album.h"
@implementation DataBaseHandler


+ (DataBaseHandler *)shareInstance
{
    static DataBaseHandler *dbHandler = nil;
    if (dbHandler == nil) {
        dbHandler = [[DataBaseHandler alloc] init];
    }
    return dbHandler;
}

- (void)judgeResult:(int)result type:(NSString *)type
{
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", type);
    } else {
        NSLog(@"%@失败, 失败编号: %d", type, result);
    }

}

- (void)openDB
{
    NSString *paths = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)lastObject];
    NSString *dbPath = [paths stringByAppendingPathComponent:@"xm.db"];
    
    NSLog(@"%@", dbPath);
    
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);    
    [self judgeResult:result type:@"打开的数据库"];
}


- (void)closeDB
{
    int result = sqlite3_close(dbPoint);
    [self judgeResult:result type:@"关闭数据库"];
}


- (void)creatTableSongCollection
{
    NSString *sqlStr = @"create table songcollection (title text, author text, songID text primary key)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"创建歌曲收藏列表"];
    
}

- (void)creatTableRecentPlay
{
    NSString *sqlStr = @"create table recentPlay (title text, author text, songID text primary key)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"创建最近播放列表"];
    
}


- (void)creatTableFMalbumCollection
{
    NSString *sqlStr = @"create table FMcollection (image text, title text, albumID text primary key)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"创建电台收藏列表"];
}

- (void)deleteTableRecentPlay
{
    NSString *sqlStr = @"drop table recentPlay";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"删除最近播放表"];

}

- (void)insertSong:(Song *)song
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into songcollection values ('%@', '%@', '%@')", song.title, song.author, song.song_id];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"收藏歌曲"];
    
}

- (void)insertRecentPlay:(Song *)song
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into recentPlay values ('%@', '%@', '%@')", song.title, song.author, song.song_id];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"添加最近播放"];
}

- (void)insertFMalbum:(FMAlbum *)fmAlbum
{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into FMcollection values ('%@', '%@', '%@')", fmAlbum.albumCoverUrl290, fmAlbum.title, fmAlbum.albumID];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"收藏电台"];
    
}


- (void)deleteSong:(Song *)song
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from songcollection where songID = '%@'", song.song_id];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"取消收藏歌曲"];
}

- (void)deleteRecentPlayWithSongID:(NSString *)songID
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from recentPlay where songID = '%@'", songID];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"删除播放记录"];
}

- (void)deleteFMalbum:(FMAlbum *)fmAlbum
{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from FMcollection where albumID = '%@'", fmAlbum.albumID];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"取消收藏电台"];
}

- (void)deleteTableFilePath
{
    NSString *str = @"drop table FilePath";
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"删除添加歌曲歌手名表"];
}
- (NSMutableArray *)selectAllInSongCollection
{
    NSString *sqlStr = @"select * from songcollection";
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(dbPoint, [sqlStr UTF8String], - 1, &stmt, NULL);
    
    NSMutableArray *songArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            Song *song = [[Song alloc] init];
            
            const unsigned char *titleChar = sqlite3_column_text(stmt, 0);
            NSString *title = [NSString stringWithUTF8String:(const char *)titleChar];
            song.title = title;
            song.author = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            song.song_id = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            
            [songArr addObject:song];
            [song release];
        }
        
    }
        sqlite3_finalize(stmt);
        return songArr;


}

- (NSMutableArray *)selectAllInRecentPlay
{
    
    NSString *sqlStr = @"select * from recentPlay";
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(dbPoint, [sqlStr UTF8String], - 1, &stmt, NULL);
    
    NSMutableArray *songArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            Song *song = [[Song alloc] init];
            
            const unsigned char *titleChar = sqlite3_column_text(stmt, 0);
            NSString *title = [NSString stringWithUTF8String:(const char *)titleChar];
            song.title = title;
            song.author = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            song.song_id = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            
            [songArr addObject:song];
            [song release];
        }
        
    }
    sqlite3_finalize(stmt);
    return songArr;

    
    
}



- (NSMutableArray *)selectAllInFMalbumCollection
{
    NSString *sqlStr = @"select * from FMcollection";
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare(dbPoint, [sqlStr UTF8String], - 1, &stmt, NULL);
    
    NSMutableArray *albumArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            FMAlbum *album = [[FMAlbum alloc] init];
            
            album.albumCoverUrl290 = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            album.title = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            album.albumID = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            
            [albumArr addObject:album];
            [album release];
        }
    }
    sqlite3_finalize(stmt);
    return albumArr;
}

- (void)creatTableFilePath
{
    NSString *str = @"create table FilePath (filePath text, singerName text, songName text, songID text primary key)";
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"创建歌曲路径表"];
}
- (void)creatTableLoading
{
    NSString *str = @"create table Loading (songName text, singer text, songID text primary key)";
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"创建正在下载表"];
}

- (void)insertDownLoadingSong:(Song *)song
{
    NSString *str = [NSString stringWithFormat:@"insert into Loading values ('%@', '%@', '%@')", song.title, song.author, song.song_id];
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"插入正在下载表数据"];
}


- (void)insertFilePath:(NSString *)filePath singer:(NSString *)singer songName:(NSString *)songName songID:(NSString *)songID
{
    NSString *str = [NSString stringWithFormat:@"insert into FilePath values ('%@', '%@', '%@', '%@')", filePath, singer, songName, songID];
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"添加一条文件存储路径"];
}

- (void)downLoadSongWithSongID:(NSString *)songID
{
    NSString *netAddress = [NSString stringWithFormat:@"http://tingapi.ting.baidu.com/v1/restserver/ting?from=webapp_music&method=baidu.ting.song.play&format=json&callback=&songid=%@&_=1413017198449", songID];
    [NetHandler getDataWithUrl:netAddress completion:^(id dict) {
        
        NSString *fileStr = [[dict objectForKey:@"bitrate"] objectForKey:@"file_link"];
        NSDictionary *songDic = [dict objectForKey:@"songinfo"];
        
        self.song = [[Song alloc] initWithDictionary:songDic];

        fileStr = [fileStr stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding];
        NSURL *fileUrl = [NSURL URLWithString:fileStr];
        NSMutableURLRequest *fileRequest = [[NSMutableURLRequest alloc] initWithURL:fileUrl cachePolicy:NSURLRequestUseProtocolCachePolicy timeoutInterval:60];
        NSURLConnection *connection = [[NSURLConnection alloc] initWithRequest:fileRequest delegate:self];
        if (connection) {
            // 初始化数据
            self.receiveData = [NSMutableData data];
            
        } else {
            NSLog(@"Bad coonection");
        }
        [connection release];
        [fileRequest release];
    }];
    
}

// 协议方法
- (void)connection:(NSURLConnection *)connection didReceiveResponse:(NSURLResponse *)response
{
    [self.receiveData setLength:0]; // 置空数据
    long long mp3Size = [response expectedContentLength];// 获取下载文件的长度
    NSLog(@"ll = %lld", mp3Size);
    DataBaseHandler *dbHander = [DataBaseHandler shareInstance];
    [dbHander insertDownLoadingSong:self.song];
    
}
// 接收数据
- (void)connection:(NSURLConnection *)connection didReceiveData:(NSData *)data
{
    [self.receiveData appendData:data];
    
}

// 数据缓存完成,保存数据
- (void)connectionDidFinishLoading:(NSURLConnection *)connection
{
    NSString *cachePath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)lastObject];
    
    NSString *filePath = [NSString stringWithFormat:@"%@/%@.mp3", cachePath, self.song.song_id];
    
    //在cache下创建文件
    NSFileManager *fileManager = [NSFileManager defaultManager];
    [fileManager createFileAtPath:filePath contents:nil attributes:nil];
    NSLog(@"mp3 path=%@",filePath);
    
    //将下载的数据，写入文件中
    [_receiveData writeToFile:filePath atomically:YES];
    
    DataBaseHandler *dbHandler = [DataBaseHandler shareInstance];
    
    [dbHandler deleteTableLoading:self.song.song_id];
    
    [dbHandler insertFilePath:filePath singer:self.song.author songName:self.song.title songID:self.song.song_id];
    
    
    
    NSNotificationCenter *center = [NSNotificationCenter defaultCenter];
    [center postNotificationName:@"downLoadComplite" object:nil];
    
    MusicPlayViewController *musicVC = [MusicPlayViewController shareInstance];
    [musicVC.downloadButton setBackgroundImage:[UIImage imageNamed:@"yixiazai.png"] forState:UIControlStateNormal];
    
}



- (void)deleteTableFilePath:(NSString *)songID
{
    NSString *str = [NSString stringWithFormat:@"delete from FilePath where songID = '%@'", songID];
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"删除数据"];
}

- (void)deleteTableLoading:(NSString *)songID
{
    NSString *str = [NSString stringWithFormat:@"delete from Loading where songID = '%@'", songID];
    int result = sqlite3_exec(dbPoint, [str UTF8String], NULL, NULL, NULL);
    [self judgeResult:result type:@"删除正在下载数据"];
}
- (NSMutableArray *)selectAllInFilePath
{
    NSString *str = @"select *from FilePath";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(dbPoint, [str UTF8String], -1, &stmt, NULL);
    NSMutableArray *filePathArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
        
            Song *song = [[Song alloc] init];
            
            const unsigned char *filePathChar = sqlite3_column_text(stmt, 0);
            NSString *filePathName = [NSString stringWithUTF8String:(const char *)filePathChar];
            song.localFilePath = filePathName;
            NSString *filePathStr = [NSString stringWithUTF8String:(const char *)filePathChar];
            song.localFilePath = filePathStr;
            
            
            
            const unsigned char *singerChar = sqlite3_column_text(stmt, 1);
            NSString *singerName = [NSString stringWithUTF8String:(const char*)singerChar];
            song.author = singerName;
            
            const unsigned char *nameChar = sqlite3_column_text(stmt, 2);
            NSString *songName = [NSString stringWithUTF8String:(const char *)nameChar];
            song.title = songName;
            
            const unsigned char *songIDChar = sqlite3_column_text(stmt, 3);
            NSString *songIDName = [NSString stringWithUTF8String:(const char *)songIDChar];
            song.song_id = songIDName;
            
            
            [filePathArr addObject:song];
            [song release];
            
            
        }
    }
    sqlite3_finalize(stmt);
    return filePathArr;
}
- (NSMutableArray *)selectAllInLoading
{
    NSString *str = @"select *from Loading";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare(dbPoint, [str UTF8String], -1, &stmt, NULL);
    NSMutableArray *loadingArr = [NSMutableArray array];
     if (result == SQLITE_OK) {
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Song *song = [[Song alloc] init];
        const unsigned char *nameChar = sqlite3_column_text(stmt, 0);
        NSString *songName = [NSString stringWithUTF8String:(const char *)nameChar];
        song.title = songName;
        
        const unsigned char *singerChar = sqlite3_column_text(stmt, 1);
        NSString *singerName = [NSString stringWithUTF8String:(const char *)singerChar];
        song.author = singerName;
        
        const unsigned char *songIDChar = sqlite3_column_text(stmt, 2);
        NSString *songIDName = [NSString stringWithUTF8String:(const char*)songIDChar];
        song.song_id = songIDName;
        
        
        [loadingArr addObject:song];
        [song release];
       }
     }
    sqlite3_finalize(stmt);
    return loadingArr;
}
@end
